package com.example.calcitedemo;

import com.manticore.jsqlformatter.JSQLFormatter;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class JSqlParserMain {

    public static void main(String[] args) throws Exception {
        testInsert();
        System.out.println("=========================================");

        testUpdate();
        System.out.println("=========================================");

        testSelect();
        System.out.println("=========================================");

    }

    public static void testUpdate() throws JSQLParserException {
        String SQL = "UPDATE t_user SET email = '373675032@qq.com', phone = '10086' WHERE id = 1";
        Update update = (Update) CCJSqlParserUtil.parse(SQL);
        System.out.println("【更新目标表】：" + update.getTable());
        List<UpdateSet> updateSets = update.getUpdateSets();
        for (UpdateSet updateSet : updateSets) {
            System.out.println("【更新字段】：" + updateSet.getColumns());
            System.out.println("【更新字】：" + updateSet.getValues());
        }
        System.out.println("【更新条件】：" + update.getWhere());
        System.out.println("--------------------------------------------------------");
        // 去掉更新手机号
        updateSets.remove(1);
        // 添加更新字段
        UpdateSet updateSet = new UpdateSet();
        updateSet.add(new Column("update_time"), new LongValue(System.currentTimeMillis()));
        updateSets.add(updateSet);
        // 更新 Where 条件
        AndExpression expression = new AndExpression();
        expression.withLeftExpression(update.getWhere());
        EqualsTo equalsTo = new EqualsTo();
        equalsTo.setLeftExpression(new Column("deleted"));
        equalsTo.setRightExpression(new LongValue(0));
        expression.withRightExpression(equalsTo);
        update.setWhere(expression);
        System.out.println("【处理后 SQL】" + update);
    }
    public static void testInsert() throws JSQLParserException {
        String SQL = "INSERT INTO t_user (role_id, user_name, email, age, sex, register_time )\n" +
                "VALUES ( 1, 'xw', 'isxuwei@qq.com', 25, '男', '2024-04-12 17:37:18' );";
        Insert insert = (Insert) CCJSqlParserUtil.parse(SQL);
        System.out.println("【插入目标表】：" + insert.getTable());
        System.out.println("【插入字段】：" + insert.getColumns());
        System.out.println("【插入值】：" + insert.getValues());
        System.out.println("--------------------------------------------------------");
        ExpressionList<Column> columns = insert.getColumns();
        ExpressionList<Expression> values = (ExpressionList<Expression>) insert.getValues().getExpressions();
        // 字段和值是一一对应的，把性别删除掉
        columns.remove(4);
        values.remove(4);
        // 新增一列状态，默认为 create
        columns.add(new Column("status"));
        values.add(new StringValue("create"));
        // 更新年龄字段 +1
        Expression expression = values.get(3);
        LongValue longValue = (LongValue) expression;
        longValue.setValue(longValue.getValue() + 1);
        System.out.println("【处理后 SQL】" + insert);
    }
    public static void testSelect() throws Exception {
        String SQL = "SELECT DISTINCT u.id, r.role_name, u.user_name, u.sex, u.email " +
                "FROM t_user u " +
                "LEFT JOIN t_role r ON u.role_id = r.id " +
                "WHERE r.role_name = '管理员' " +
                "ORDER BY u.age DESC " +
                "LIMIT 0,10";

        String sql2 = "select\n" +
                "    r.id,\n" +
                "    r.region_name as regionName,\n" +
                "    r.map as map,\n" +
                "    r.staff,\n" +
                "    r.people_max as peopleMax,\n" +
                "    r.create_by,\n" +
                "    r.create_time,\n" +
                "    r.remark,\n" +
                "    u.user_name,\n" +
                "    CONCAT(u.nick_name, '（', u.user_name, '）') as staffCodeAndName,\n" +
                "    m.map_name as mapName\n" +
                "from region r left join sys_user u  on r.staff = u.user_id left join map_url m on r.map = m.id\n" +
                "WHERE r.del_flag = '0' and r.id = 1111";

        String formattedSql = JSQLFormatter.format(sql2);
        System.out.println(formattedSql);
        PlainSelect select = (PlainSelect) CCJSqlParserUtil.parse(formattedSql);
        PlainSelect plainSelect = select.getPlainSelect();
        System.out.println("【DISTINCT 子句】：" + plainSelect.getDistinct());
        System.out.println("【查询字段】：" + plainSelect.getSelectItems());
        System.out.println("【FROM 表】：" + plainSelect.getFromItem());
        System.out.println("【WHERE 子句】：" + plainSelect.getWhere());
        System.out.println("【JOIN 子句】：" + plainSelect.getJoins());
        System.out.println("【LIMIT 子句】：" + plainSelect.getLimit());
        System.out.println("【OFFSET 子句】：" + plainSelect.getOffset());
        System.out.println("【ORDER BY 子句】：" + plainSelect.getOrderByElements());
        System.out.println("--------------------------------------------------------");
        // 取消去重
        plainSelect.setDistinct(null);
        // 修改查询字段为 *
        List<SelectItem<?>> selectItems = new ArrayList<>();
        selectItems.add(new SelectItem<>(new AllColumns()));
        plainSelect.setSelectItems(selectItems);

        // 修改 WHERE 子句
        EqualsTo equalsTo = new EqualsTo();
        equalsTo.setLeftExpression(new Column("u.id"));
        equalsTo.setRightExpression(new LongValue(1));
        plainSelect.setWhere(equalsTo);

        // 修改 LIMIT 子句
        Limit limit = new Limit();
        limit.setRowCount(new LongValue(5));
        limit.setOffset(new LongValue(0));
        plainSelect.setLimit(limit);

        // 修改排序为 u.age ASC
        OrderByElement orderByElement = new OrderByElement();
        orderByElement.setExpression(new Column("u.age"));
        orderByElement.setAsc(true); // 升序
        plainSelect.setOrderByElements(Collections.singletonList(orderByElement));
        System.out.println("【处理后 SQL】" + plainSelect);

        PlainSelect select2 = (PlainSelect) CCJSqlParserUtil.parse(sql2);

        SelectItem selectItem = select2.getSelectItems().get(0);
        System.out.println(selectItem);

        Table table = (Table) select2.getFromItem();
        System.out.println(table);

        System.out.println("a="+equalsTo.getLeftExpression()+", b="+equalsTo.getRightExpression());
    }
}
